/*********************************************************************************
 *      Copyright:  (C) 2025 LingYun IoT System Studio
 *                  All rights reserved.
 *
 *       Filename:  sqlite3.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(2025年04月25日)
 *         Author:  lijiahui <2199250859@qq.com>
 *      ChangeLog:  1, Release initial version on "2025年04月25日 16时10分31秒"
 *                 
 ********************************************************************************/

#include "sqlite3_handler.h"


int init_db(const char* db_path, sqlite3** db)
{
	int 			rc = -1;
	const char		*sql_create;
	char			*errmsg = NULL;
	rc=sqlite3_open(db_path, db);

	if(rc != SQLITE_OK)
	{
		log_error("Cannot open DB:%s\n", strerror(errno));
		return -1;
	}

	sql_create=
		"CREATE TABLE IF NOT EXISTS temperature_data("
		"id INTEGER PRIMARY KEY AUTOINCREMENT,"
		"device_id TEXT NOT NULL,"
		"time TEXT NOT NULL,"
		"temperature TEXT NOT NULL);";

	rc=sqlite3_exec(*db, sql_create, NULL, NULL, &errmsg);
	if(rc != SQLITE_OK)
	{
		log_error("Create table failed:%s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(*db);
		*db = NULL;//避免野指针
		return -2;
	}

	return 0;
}


//存储一条记录到数据库（将数据包pack_t保存到数据库的temperature_data表中）
int save_pack_to_db(sqlite3 *db, const pack_t *pack)
{
	const char 		*sql;
	sqlite3_stmt 	*stmt;
	int 			rv = -1;
	int 			total_inset = 0;

	sql="INSERT INTO temperature_data(device_id,time,temperature)VALUES(?,?,?);";//准备SQL语句
	
	if(sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)//预处理语句，sqlite3_prepare_v2用于将SQL语句编译为字节码
	{
		return -1;
	}

	sqlite3_bind_text(stmt, 1, pack->id, -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt, 2, pack->systime, -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt, 3, pack->temp, -1, SQLITE_STATIC);

	rv = sqlite3_step(stmt);//执行预处理好的SQL语句
	sqlite3_finalize(stmt);//释放预处理语句占用的资源

	/*if(rv==SQLITE_DONE)
	{
		total_inset++;
		printf("inset successfully,total insert rows:%d\n",total_inset);
		return 0;
	}*/


	return (rv==SQLITE_DONE)?0:-2;
}



//查询第一条数据
int fetch_first_pack(sqlite3 *db, pack_t *pack_out, int *db_id_out)
{
	const char 	*sql = "SELECT id, device_id, time, temperature FROM temperature_data ORDER BY id ASC LIMIT 1;";//获取第一条数据
	sqlite3_stmt *stmt;
	int 		rv = -1;

	if(sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
	{
		return -1;
	}

	rv = sqlite3_step(stmt);
	if(rv == SQLITE_ROW)
	{
		if(db_id_out)
		{
			*db_id_out = sqlite3_column_int(stmt, 0);//读取第零列,即id
		}

		snprintf(pack_out->id, sizeof(pack_out->id), "%s",sqlite3_column_text(stmt, 1));
		snprintf(pack_out->systime, sizeof(pack_out->systime), "%s",sqlite3_column_text(stmt, 2));
		snprintf(pack_out->temp, sizeof(pack_out->temp), "%s",sqlite3_column_text(stmt, 3));

		sqlite3_finalize(stmt);
		return 0;
	}

	sqlite3_finalize(stmt);
	return -2;//没有数据
}

int delete_pack_by_id(sqlite3 *db, int id)
{
	const char 		*sql;
	sqlite3_stmt 	*stmt;
	int 			rv = -1;
	sql="DELETE FROM temperature_data WHERE id=?;";
	
	if(sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
	{
		return -1;
	}

	sqlite3_bind_int(stmt, 1, id);//第二个参数：参数的索引（从1开始，即第一个？时1，第二个是2，以此类推）
	
	rv = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return rv==SQLITE_DONE?0:1;
}


